package org.magicsoft.office.excel.impt;

import java.sql.Connection;
import java.util.Map;

import org.magicsoft.office.db.DatabaseType;
import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.Column;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExistRecordsUpdater {

	private static final Logger logger = LoggerFactory.getLogger(ExistRecordsUpdater.class);

	public void update(Sheet sheet, Connection conn, DatabaseType databaseType) {
		StringBuilder sqlBuilder;
		StringBuilder selectBuilder;
		for (TargetTable targetTable : sheet.getTargetTables()) {
			if (targetTable.getPrimaryKey() == null) {
				continue;
			}

			sqlBuilder = new StringBuilder();
			selectBuilder = new StringBuilder();

			sqlBuilder.append("update ");
			sqlBuilder.append(targetTable.getTableName());
			sqlBuilder.append(" set (");
			for (Map.Entry<String, Column> entry : sheet.getColumns().entrySet()) {
				if (targetTable.getTableName().equals(entry.getValue().getBindTable().getTableName())) {
					if (entry.getValue().getReference() != null && entry.getValue().getReference().getRefTableName()
							.equals(entry.getValue().getBindTable())) {
						// 自关联parentId暂时不在这里处理。
						continue;
					}

					if (entry.getValue().getReference() != null) {
						sqlBuilder.append(entry.getValue().getBindColumn());
						sqlBuilder.append(", ");

						selectBuilder.append(sheet.getTempTableName());
						selectBuilder.append(".");
						selectBuilder.append(entry.getValue().getReference().getTempTableColumn());
						selectBuilder.append(", ");
					} else if (entry.getValue().getBindColumn() != null) {
						sqlBuilder.append(entry.getValue().getBindColumn());
						sqlBuilder.append(", ");

						selectBuilder.append(sheet.getTempTableName());
						selectBuilder.append(".");
						selectBuilder.append(entry.getValue().getTempTableColumn());
						selectBuilder.append(", ");
					}
				}
			}
			sqlBuilder.deleteCharAt(sqlBuilder.length() - 2);// -2因为包含一个空格，包含空格是为了超长时在表格中方便换行。
			selectBuilder.deleteCharAt(selectBuilder.length() - 2);

			sqlBuilder.append(") = (select ");
			sqlBuilder.append(selectBuilder);
			sqlBuilder.append(" from ");
			sqlBuilder.append(sheet.getTempTableName());
			sqlBuilder.append(" where ");
			sqlBuilder.append(targetTable.getNidColumn());
			sqlBuilder.append(" = ");
			sqlBuilder.append(targetTable.getTableName());
			sqlBuilder.append(".");
			sqlBuilder.append(targetTable.getPrimaryKey().getColumn());
			sqlBuilder.append(" and ");
			sqlBuilder.append(targetTable.getOidColumn());
			sqlBuilder.append(" is not null and errormsg_ is null)");
			String sql = DatabaseUtils.translateJoinUpdate(sqlBuilder.toString(), databaseType);
			logger.debug("update exist records sql:" + sql);
			DatabaseUtils.executeUpdate(conn, sql);
		}
	}

}
